package ecommerce.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ecommerce.database.DataSource;
import ecommerce.model.User;

public class UserDAOImpl implements UserDAO {

	public List<User> getAllUsers() {
		PreparedStatement ps = null;
		List<User> users = new ArrayList<User>();
		try {
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement("Select * from users;");
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				User u = new User();
				u.setUsername(rs.getString("user_name"));
				u.setPhotosViewed(rs.getInt("photos_viewed"));
				u.setQueriesSubmitted(rs.getInt("queries_submitted"));
				users.add(u);
			}
			rs.close();
			ps.close();
			connection.close();
			return users;
		} catch (SQLException ex) {
			return null;
		}
	}
	
	public List<User> getMembers() {
		PreparedStatement ps = null;
		List<User> users = new ArrayList<User>();
		try {
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement("Select * from users where role_id != 1;");
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				User u = new User();
				u.setUsername(rs.getString("user_name"));
				u.setPhotosViewed(rs.getInt("photos_viewed"));
				u.setQueriesSubmitted(rs.getInt("queries_submitted"));
				users.add(u);
			}
			rs.close();
			ps.close();
			connection.close();
			return users;
		} catch (SQLException ex) {
			return null;
		}
	}
	
	public User getUserById(int user_id) {
		PreparedStatement ps = null;
		User user = null;
		try {
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement("Select * from users where user_id = ?;");
			ps.setInt(1, user_id);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				user = new User();
				user.setUsername(rs.getString("user_name"));
				user.setPhotosViewed(rs.getInt("photos_viewed"));
				user.setQueriesSubmitted(rs.getInt("queries_submitted"));
			}
			rs.close();
			ps.close();
			connection.close();
			return user;
		} catch (SQLException ex) {
			return user;
		}
	}
	
	public String getUserRole(int user_id){
		PreparedStatement ps = null;
		String role = null;
		try {
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement("SELECT roles.role_name FROM roles, users WHERE users.user_id = ?" +
					" AND users.role_id = roles.role_id;");
			ps.setInt(1, user_id);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				role = rs.getString("role_name");
			}
			rs.close();
			ps.close();
			connection.close();
			return role;
		} catch (SQLException ex) {
			return role;
		}
	}
	
	public boolean updateQueriesSubmitted(int user_id) {
		User currentData = getUserById(user_id);
		PreparedStatement ps = null;
		try {
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement("UPDATE users set queries_submitted = ? WHERE user_id = ?;");
			ps.setInt(1, (currentData.getQueriesSubmitted() + 1));
			ps.setInt(2, user_id);
			ps.executeUpdate();
			ps.close();
			connection.close();
			return true;
		} catch (SQLException ex) {
			return false;
		}
	}
	
	public boolean updatePhotosViewed(int user_id) {
		User currentData = getUserById(user_id);
		PreparedStatement ps = null;
		try {
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement("Update users set photos_viewed = ? Where user_id = ?;");
			ps.setInt(1, (currentData.getPhotosViewed() + 1));
			ps.setInt(2, user_id);
			ps.executeUpdate();
			ps.close();
			connection.close();
			return true;
		} catch (SQLException ex) {
			return false;
		}
	}
	
	public User authenticate(String username, String password) {
		PreparedStatement ps = null;
		try {
			String sql = "Select * from users where user_name = ? and user_password = ?";
			Connection connection = DataSource.getInstance().getConnection();
			ps = connection.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			ResultSet rs = ps.executeQuery();
			if(rs.next() == false) {
				ps.close(); 
				connection.close();
				return null;
			} else {
				User u = new User();
				u.setUserId(rs.getInt(1));
				u.setUsername(rs.getString(2));
				u.setPassword(rs.getString(3));
				u.setQueriesSubmitted(rs.getInt(4));
				u.setPhotosViewed(rs.getInt(5));
				ps.close();
				connection.close();
				return u;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
}
